﻿Namespace ExcelOperations
    Public Class XLSTranslator
        Private Const XLSTranslatorAutomationCopyright = "Automation provided by XLSTranslator for L3D, (C)2010-2011 Vasco Mantellini (v.1.0)"
        Public ReadOnly Property AutomationCopyright
            Get
                Return XLSTranslatorAutomationCopyright
            End Get
        End Property
        Public Sub BuildResumeTable(ByVal PresentationObject As L3DObjectsLibrary.L3DPlatformPlan, ByVal FileName As String)
            Dim pExcel As New Microsoft.Office.Interop.Excel.Application
            pExcel.DisplayAlerts = False
            pExcel.Visible = False
            If pExcel.Version >= 10 Then
                FileName = String.Format("{0}{1}", FileName, "x")
            End If
            Dim TableSheet As New Microsoft.Office.Interop.Excel.Worksheet
            For Each Worksheet As Microsoft.Office.Interop.Excel.Worksheet In pExcel.Workbooks.Add.Worksheets
                If Worksheet IsNot pExcel.Workbooks(1).Worksheets(1) Then
                    Worksheet.Delete()
                End If
            Next
            For Each Project As L3DObjectsLibrary.Project In PresentationObject.Projects
                If FasiEOBD(Project).Count > 0 Then
                    If Project IsNot PresentationObject.Projects.First Then
                        TableSheet = pExcel.Workbooks(1).Worksheets.Add(, pExcel.Workbooks(1).Worksheets(pExcel.Workbooks(1).Worksheets.Count))
                    Else
                        TableSheet = pExcel.Workbooks(1).Worksheets(1)
                    End If
                    With TableSheet
                        .Name = StringToWorksheetName(Project.Name)
                    End With
                    BuildTableLayout(TableSheet, Project, PresentationObject.PresentationStatusDate)
                End If
            Next
            pExcel.Workbooks(1).SaveAs(FileName)
            pExcel.Quit()
            VBACustomOperations.ExcelGenericOperations.TerminateExcelProcess(pExcel)
            pExcel = Nothing
            TableSheet = Nothing
        End Sub
        Private Function FasiEOBD(ByVal Project As L3DObjectsLibrary.Project) As IEnumerable(Of L3DObjectsLibrary.ProjectElement)
            Dim Fasi = From el As L3DObjectsLibrary.ProjectElement In Project.ProjectElements
               Select el
               Where el.HasEOBDCar = True
            Return Fasi
        End Function

        Private Sub BuildTableLayout(ByVal TableSheet As Microsoft.Office.Interop.Excel.Worksheet, ByVal Project As L3DObjectsLibrary.Project, ByVal StatusDate As Date)
            Dim NomeFont As String = "Arial"
            Dim DataJOB1 As Date = Project.GetSpecialDate("sop")
            Dim DataDAP As Date = Project.GetSpecialDate("dap")
            If TableSheet.Application.Workbooks(1).Worksheets.Count = 1 Then
                Dim Style As Microsoft.Office.Interop.Excel.Style = TableSheet.Application.Workbooks(1).Styles.Add("l3dCellStyleTitolo1")
                With Style
                    .WrapText = True
                    .Font.Name = NomeFont
                    .Font.Bold = True
                    .Font.Size = 18
                    .HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
                    .VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter
                End With
                Style = TableSheet.Application.Workbooks(1).Styles.Add("l3dCellStyleTitolo2")
                With Style
                    .WrapText = True
                    .Font.Name = NomeFont
                    .Font.Bold = True
                    .Font.Size = 14
                    .HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
                    .VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter
                End With
                Style = TableSheet.Application.Workbooks(1).Styles.Add("l3dCellStyleTitolo3")
                With Style
                    .WrapText = True
                    .Font.Name = NomeFont
                    .Font.Bold = True
                    .Font.Size = 12
                    .HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
                    .VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter
                End With
                Style = TableSheet.Application.Workbooks(1).Styles.Add("l3dCellStyleFirma")
                With Style
                    .Font.Name = NomeFont
                    .Font.Bold = False
                    .Font.Italic = True
                    .Font.Size = 8
                    .HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft
                    .VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter
                End With
                Style = TableSheet.Application.Workbooks(1).Styles.Add("l3dCellStyleNormal")
                With Style
                    .WrapText = True
                    .Font.Name = NomeFont
                    .Font.Bold = False
                    .Font.Size = 12
                    .HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
                    .VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter
                End With
            End If
            Dim NumeroRighe As Integer = 6
            Dim Testo As String = ""
            Dim RigaTotale As Integer = 4 + FasiEOBD(Project).Count
            NumeroRighe = NumeroRighe + FasiEOBD(Project).Count
            With TableSheet
                .Rows(1).rowheight = 60
                .Rows(2).rowheight = 47
                .Rows(3).rowheight = 27
                For Riga = 4 To RigaTotale - 1
                    .Rows(Riga).rowheight = 55
                Next
                .Rows(RigaTotale).rowheight = 25
                .Rows(RigaTotale + 1).rowheight = 80
                .Rows(RigaTotale + 2).rowheight = 25
                .Columns(1).ColumnWidth = 19
                .Columns(2).ColumnWidth = 19
                For Colonna = 3 To 8
                    .Columns(Colonna).columnwidth = 12
                Next
                .Columns(9).columnwidth = 18
                .Columns(10).columnwidth = 18
                With .Range("C1", "H1")
                    .Merge()
                    .Value = Project.Name & Chr(10) & "JOB1 " & Project.GetSpecialDate("sop", False)
                    .Style = "l3dCellStyleTitolo1"
                End With
                BordoCella(.Range("C1", "H1"), 15, 15)
                For Column = 9 To 10
                    With .Cells(1, Column)
                        .Style = "l3dCellStyleTitolo2"
                        Select Case Column
                            Case 9
                                .value = "km" & Chr(10) & "consuntivo"
                            Case 10
                                .value = "data" & Chr(10) & "consuntivo"
                        End Select
                    End With
                    .Range(.Cells(1, Column), .Cells(3, Column)).Merge()
                    BordoCella(.Cells(1, Column), 15, 15)
                    BordoCella(.Range(.Cells(1, Column), .Cells(NumeroRighe, Column)), 47, 47)
                Next
                .Range("C2", "H2").WrapText = True
                For Column = 3 To 7 Step 2
                    With .Cells(2, Column)
                        .Style = "l3dCellStyleTitolo3"
                        Select Case Column
                            Case 3
                                .value = "Standard minimo al VSOP"
                            Case 5
                                .value = "Accumulo previsto alla DAP" & Chr(10) & Format(Project.GetSpecialDate("dap"), "dd/MM/yyyy")
                            Case 7
                                .value = "Accumulo al JOB1" & Chr(10) & Format(Project.GetSpecialDate("sop"), "dd/MM/yyyy")
                        End Select
                    End With
                    .Range(.Cells(2, Column), .Cells(2, Column + 1)).Merge()
                    .Cells(3, Column).value = "[km]"
                    .Cells(3, Column + 1).value = "%"
                    With .Range(.Cells(2, Column), .Cells(3, Column + 1))
                        .Style = "l3dCellStyleTitolo3"
                    End With
                    BordoCella(.Range(.Cells(2, Column), .Cells(3, Column + 1)), 63, 15)
                    BordoCella(.Range(.Cells(3, Column), .Cells(RigaTotale + 2, Column + 1)), 63, 45)
                Next
                For Riga = 4 To RigaTotale - 1
                    For Each Car As L3DObjectsLibrary.Car In FasiEOBD(Project)(Riga - 4).AllCars(True, False)
                        .Cells(Riga, 2).Value = .Cells(Riga, 2).Value & Car.Name
                        .Cells(Riga, 9).value = .Cells(Riga, 9).Value & Car.Name & "   " & Car.kmStatus
                        If Car IsNot FasiEOBD(Project)(Riga - 4).AllCars(True, False)(FasiEOBD(Project)(Riga - 4).AllCars(True, False).Count) Then
                            .Cells(Riga, 2).Value = .Cells(Riga, 2).Value & Chr(10)
                            .Cells(Riga, 9).value = .Cells(Riga, 9).Value & Chr(10)
                        End If
                        .Rows(Riga).autofit()
                    Next
                Next
                With .Cells(RigaTotale, 1)
                    .value = "TOTALE"
                    .style = "l3dCellStyleTitolo2"
                End With
                With .Cells(RigaTotale + 1, 1)
                    .value = "RG"
                    .style = "l3dCellStyleTitolo2"
                    For Each Car As L3DObjectsLibrary.Car In Project.AllCars(False, True)
                        .Cells(1, 2).Value = .Cells(1, 2).Value & Car.Name
                        .Cells(1, 9).value = .Cells(1, 9).Value & Car.Name & "   " & Car.kmStatus
                        If Car IsNot Project.AllCars(False, True)(Project.AllCars(False, True).Count) Then
                            .cells(1, 2).value = .cells(1, 2).value & Chr(10)
                            .Cells(1, 9).value = .Cells(1, 9).Value & Chr(10)
                        End If
                        .rows(1).autofit()
                    Next
                End With
                With .Cells(RigaTotale + 2, 1)
                    .value = "TOT. Flotte EOBD+ RG"
                    .style = "l3dCellStyleTitolo2"
                End With
                With .Range("A4")
                    .Value = "Flotte EOBD"
                    .WrapText = True
                    .Style = "l3dCellStyleTitolo2"
                End With
                .Range(.Cells(4, 1), .Cells(RigaTotale - 1, 1)).Merge()
                For Row = RigaTotale To RigaTotale + 2 Step 2
                    .Range(.Cells(Row, 1), .Cells(Row, 2)).Merge()
                Next
                BordoCella(.Range(.Cells(4, 1), .Cells(RigaTotale + 2, 2)), 63, 63)
                With .Range(.Cells(RigaTotale + 3, 1), .Cells(RigaTotale + 3, 10))
                    .Merge()
                    .Value = AutomationCopyright
                    .Style = "l3dCellStyleFirma"
                End With
            End With
            Dim Fasi = FasiEOBD(Project)
            Dim Fase As New L3DObjectsLibrary.ProjectElement
            Dim Percentage As Integer
            For Riga = 4 To 3 + Fasi.Count
                Fase = Fasi(Riga - 4)
                TableSheet.Cells(Riga, 5).value = Fase.MileageForecast(StatusDate, DataDAP, True)
                TableSheet.Cells(Riga, 6).value = Int(Fase.MileageForecast(StatusDate, DataDAP, True) / Fase.AllCarsKm(True) * 100)
                TableSheet.Cells(Riga, 7).value = Fase.MileageForecast(StatusDate, DataJOB1, True)
                TableSheet.Cells(Riga, 8).value = Int(Fase.MileageForecast(StatusDate, DataJOB1, True) / Fase.AllCarsKm(True) * 100)
                TableSheet.Cells(4 + Fasi.Count, 5).value = CInt(TableSheet.Cells(4 + Fasi.Count, 5).value) + CInt(TableSheet.Cells(Riga, 5).value)
                TableSheet.Cells(4 + Fasi.Count, 7).value = CInt(TableSheet.Cells(4 + Fasi.Count, 7).value) + CInt(TableSheet.Cells(Riga, 7).value)
            Next
            TableSheet.Cells(4, 10).value = Format(StatusDate, "dd/MM/yyyy")
            TableSheet.Cells(5 + Fasi.Count, 5).value = Project.MileageForecast(StatusDate, DataDAP, False, True)
            If Project.AllCarsKm(False, True) = 0 Then
                Percentage = 0
            Else
                Percentage = Int(Project.MileageForecast(StatusDate, DataDAP, False, True) / Project.AllCarsKm(False, True) * 100)
            End If
            TableSheet.Cells(5 + Fasi.Count, 6).value = Percentage
            TableSheet.Cells(5 + Fasi.Count, 7).value = Project.MileageForecast(StatusDate, DataJOB1, False, True)
            If Project.AllCarsKm(False, True) = 0 Then
                Percentage = 0
            Else
                Percentage = Int(Project.MileageForecast(StatusDate, DataJOB1, False, True) / Project.AllCarsKm(False, True) * 100)
            End If
            TableSheet.Cells(5 + Fasi.Count, 8).value = Percentage
            TableSheet.Cells(6 + Fasi.Count, 5).value = CInt(TableSheet.Cells(4 + Fasi.Count, 5).value) + CInt(TableSheet.Cells(5 + Fasi.Count, 5).value)
            TableSheet.Cells(6 + Fasi.Count, 7).value = CInt(TableSheet.Cells(4 + Fasi.Count, 7).value) + CInt(TableSheet.Cells(5 + Fasi.Count, 7).value)
            TableSheet.Cells(5 + Fasi.Count, 10).value = Format(StatusDate, "dd/MM/yyyy")
        End Sub
        Private Sub BordoCella(ByVal Range As Microsoft.Office.Interop.Excel.Range, Optional ByVal Lati As Byte = 15, Optional ByVal Spessore As Byte = 0)
            Dim Edge As Integer = 0
            With Range
                For Bordo = 0 To 5
                    Edge = 2 ^ Bordo
                    If (Edge And Lati) = Edge Then
                        .Borders(Bordo + 7).LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid
                    End If
                    If (Edge And Spessore) = Edge Then
                        .Borders(Bordo + 7).Weight = 3
                    End If
                Next
            End With
        End Sub
        Private Function StringToWorksheetName(ByVal StringValue As String) As String
            Dim Chars As Array = {"[", "]", "*", "?", "/", "\"}
            For Each El As String In Chars
                If InStr(StringValue, El) > 0 Then
                    StringValue = Left(StringValue, InStr(StringValue, El) - 1)
                End If
            Next
            StringValue = Left(StringValue, 31)
            Return StringValue
        End Function
        Public Function XLSFileImport(ByVal DatasheetPath As String) As L3DObjectsLibrary.L3DPlatformPlan
            Dim pExcel As New Microsoft.Office.Interop.Excel.Application
            pExcel.DisplayAlerts = False
            Dim pFoglioDati As Microsoft.Office.Interop.Excel.Worksheet = pExcel.Workbooks.Open(DatasheetPath).Sheets(1)
            Dim GlobalPlan As New L3DObjectsLibrary.L3DPlatformPlan
            GlobalPlan.CreateControl()
            With GlobalPlan
                .Clear()
                .PresentationOwner = CStr(pFoglioDati.Cells(5, 2).value)
                .PresentationModel = CStr(pFoglioDati.Cells(3, 2).value)
                .PresentationStatusDate = CDate(pFoglioDati.Cells(4, 2).value)
            End With
            Dim pPrimaRigaPiano = 10
            Dim pUltimaRigaPiano = pPrimaRigaPiano
            Dim pNumeroApplicazione = 1
            Dim pPrimaRigaGruppo = pPrimaRigaPiano
            Dim pUltimaRigaGruppo = pPrimaRigaPiano
            Dim pRigaAnalizzata As Integer = 10
            Dim pPrimaRigaProgetto As Boolean = True
            Dim pNumeroMilestones = 0
            Dim pNumeroGruppi = 0
            Dim pNomeProgetto As String = CStr(pFoglioDati.Cells(pRigaAnalizzata, 3).value)
            Dim pProgetto As New L3DObjectsLibrary.Project
            Dim pNomeFase As String
            Dim pNuovoGruppo As New L3DObjectsLibrary.Group
            Dim pNuovaCar As L3DObjectsLibrary.Car
            Dim pNuovoElemento As New L3DObjectsLibrary.ProjectElement
            Dim pNuovaMilestone As New L3DObjectsLibrary.Milestone

            pPrimaRigaProgetto = True
            Do Until CStr(pFoglioDati.Cells(pRigaAnalizzata, 3).value) = "###" Or pRigaAnalizzata = 1000
                pProgetto = New L3DObjectsLibrary.Project
                pPrimaRigaProgetto = True
                With pProgetto
                    .Name = CStr(pFoglioDati.Cells(pRigaAnalizzata, 3).value)
                    Select Case LCase(CStr(pFoglioDati.Cells(pRigaAnalizzata + 2, 3).value))
                        Case "verde", "green"
                            .ProjectRisk = L3DObjectsLibrary.ProjectConstantValues.ProjectStatusValues.L3DStatusGreen
                        Case "giallo", "yellow"
                            .ProjectRisk = L3DObjectsLibrary.ProjectConstantValues.ProjectStatusValues.L3DStatusYellow
                        Case "rosso", "red"
                            .ProjectRisk = L3DObjectsLibrary.ProjectConstantValues.ProjectStatusValues.L3DStatusRed
                    End Select
                    .ProjectHighlights = CStr(pFoglioDati.Cells(pRigaAnalizzata + 3, 3).value)
                End With
                Do Until CStr(pFoglioDati.Cells(pRigaAnalizzata - 1, 3).value) = "###" And pPrimaRigaProgetto = False
                    If CStr(pFoglioDati.Cells(pRigaAnalizzata, 8).value) = "CL" Or pPrimaRigaProgetto = True Then
                        pPrimaRigaProgetto = False
                        pNuovoElemento = New L3DObjectsLibrary.ProjectElement
                        With pNuovoElemento
                            .Name = CStr(pFoglioDati.Cells(pRigaAnalizzata, 7).value)
                        End With
                    End If
                    If CStr(pFoglioDati.Cells(pRigaAnalizzata, 5).value) <> "" Then
                        pNuovaMilestone = New L3DObjectsLibrary.Milestone
                        With pNuovaMilestone
                            .Name = CStr(pFoglioDati.Cells(pRigaAnalizzata, 5).value)
                            .MilestoneDate = CDate(pFoglioDati.Cells(pRigaAnalizzata, 6).value)
                        End With
                        pNuovoElemento.Milestones.Add(pNuovaMilestone)
                        pRigaAnalizzata = pRigaAnalizzata + 1
                    Else
                        pNomeFase = CStr(pFoglioDati.Cells(pRigaAnalizzata, 7).value)
                        Do Until CStr(pFoglioDati.Cells(pRigaAnalizzata, 7).value) <> pNomeFase
                            pNuovoGruppo = New L3DObjectsLibrary.Group
                            Do Until CStr(pFoglioDati.Cells(pRigaAnalizzata, 8).value) = "CL" And pNuovoGruppo.Cars.Count > 0 And CStr(pFoglioDati.Cells(pRigaAnalizzata - 1, 3).value) <> "###"
                                pNuovaCar = New L3DObjectsLibrary.Car
                                With pNuovaCar
                                    If CStr(pFoglioDati.Cells(pRigaAnalizzata, 9).value) = "sì" Or CStr(pFoglioDati.Cells(pRigaAnalizzata, 9).value) = "yes" Or CStr(pFoglioDati.Cells(pRigaAnalizzata, 9).value) = "s" Or CStr(pFoglioDati.Cells(pRigaAnalizzata, 9).value) = "y" Or CStr(pFoglioDati.Cells(pRigaAnalizzata, 9).value) = "si" Then
                                        .EOBD = True
                                    End If
                                    .Name = CStr(pFoglioDati.Cells(pRigaAnalizzata, 10).value)
                                    .kmHS = CInt(pFoglioDati.Cells(pRigaAnalizzata, 11).value)
                                    .kmRG = CInt(pFoglioDati.Cells(pRigaAnalizzata, 12).value)
                                    .kmCity = CInt(pFoglioDati.Cells(pRigaAnalizzata, 13).value)
                                    .kmStatus = CInt(pFoglioDati.Cells(pRigaAnalizzata, 14).value)
                                    .CarStartDate = CDate(pFoglioDati.Cells(pRigaAnalizzata, 15).value)
                                    .CarStatus = CStr(pFoglioDati.Cells(pRigaAnalizzata, 21).value)
                                    If DateTime.Compare(CDate(pFoglioDati.Cells(pRigaAnalizzata, 17).value), .CarStartDate) < 0 Then
                                        .CarRealStartDate = .CarStartDate
                                    Else
                                        .CarRealStartDate = CDate(pFoglioDati.Cells(pRigaAnalizzata, 17).value)
                                    End If
                                    .CarEndDate = DateAdd(DateInterval.Day, CDbl(pFoglioDati.Cells(pRigaAnalizzata, 16).value) * 30, CDate(pFoglioDati.Cells(pRigaAnalizzata, 15).value))
                                    If .HasReplan = True And DateTime.Compare(CDate(pFoglioDati.Cells(pRigaAnalizzata, 19).value), .CarRealStartDate) >= 0 Then
                                        .CarRealEndDate = CDate(pFoglioDati.Cells(pRigaAnalizzata, 19).value)
                                    ElseIf .HasReplan And CStr(pFoglioDati.Cells(pRigaAnalizzata, 19).value) = "" And CDbl(pFoglioDati.Cells(pRigaAnalizzata, 18).value) > 0 Then
                                        .CarRealEndDate = DateAdd(DateInterval.Day, CDbl(pFoglioDati.Cells(pRigaAnalizzata, 18).value) * 30, .CarRealStartDate)
                                    Else
                                        .CarRealEndDate = .CarEndDate
                                    End If
                                End With
                                pNuovoGruppo.Cars.Add(pNuovaCar)
                                pRigaAnalizzata = pRigaAnalizzata + 1
                            Loop
                            pNuovoElemento.Groups.Add(pNuovoGruppo)
                        Loop
                    End If
                    pProgetto.ProjectElements.Add(pNuovoElemento)
                Loop
                pProgetto.ProjectSOPDate = pProgetto.GetSpecialDate("sop")
                pProgetto.ProjectDAPDate = pProgetto.GetSpecialDate("dap")
                GlobalPlan.Projects.Add(pProgetto)
            Loop
            pExcel.Workbooks.Close()
            pExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(pExcel)
            pFoglioDati = Nothing
            pExcel = Nothing
            Return GlobalPlan
        End Function
    End Class
    Public Module XLSFunctions
        Private Class ErrorContainer
            Private pErrors As New Microsoft.VisualBasic.Collection
            Public Property Errors As Microsoft.VisualBasic.Collection
                Get
                    Return pErrors
                End Get
                Set(ByVal value As Microsoft.VisualBasic.Collection)
                    pErrors = value
                End Set
            End Property
            Public ReadOnly Property HasErrors As Boolean
                Get
                    If Me.Errors.Count > 0 Then
                        Return True
                    Else
                        Return False
                    End If
                End Get
            End Property
            Public Sub AddError(ByVal Description As String, Optional ByVal RowCode As Integer = 0)
                Dim pErrore As New ErrorObject
                With pErrore
                    .Description = Description
                    .RowCode = RowCode
                End With
                Me.Errors.Add(pErrore)
            End Sub
            Public Function ShowErrors(Optional ByVal ShowOk As Boolean = False) As Boolean
                If Me.HasErrors = True Then
                    Dim pTesto As String = ""
                    For Each Errore As ErrorObject In Me.Errors
                        pTesto = pTesto & Errore.Description & " riga " & CStr(Errore.RowCode) & Chr(13)
                    Next
                    MsgBox(pTesto)
                    Return True
                Else
                    If ShowOk = True Then
                        MsgBox("Il file di dati ha un formato corretto")
                    End If
                    Return False
                End If
            End Function
        End Class
        Private Class ErrorObject
            Private pDescription As String
            Private pRowCode As Integer
            Public Property Description As String
                Get
                    Return pDescription
                End Get
                Set(ByVal value As String)
                    pDescription = value
                End Set
            End Property
            Public Property RowCode As Integer
                Get
                    Return pRowCode
                End Get
                Set(ByVal value As Integer)
                    pRowCode = value
                End Set
            End Property
        End Class
        Public Function FileSyntaxVerification(ByVal DataSheetPath As String) As Boolean
            Dim pErrore As New ErrorContainer
            Dim pExcel As New Microsoft.Office.Interop.Excel.Application
            Dim pFoglio As New Microsoft.Office.Interop.Excel.Worksheet
            Dim pFileCheck As Boolean = True
            pFoglio = pExcel.Workbooks.Open(DataSheetPath).Sheets(1)
            pExcel.DisplayAlerts = False
            Dim pRigaInAnalisi As Integer = 10
            Dim pProjectStart As Date
            Dim pPrimaRigaProgetto As Boolean
            Do Until CStr(pFoglio.Cells(pRigaInAnalisi, 3).value) = "###" And CStr(pFoglio.Cells(pRigaInAnalisi - 1, 3).value) = "###"
                pPrimaRigaProgetto = True
                If CStr(pFoglio.Cells(pRigaInAnalisi + 1, 3).value) = "" Then
                    pErrore.AddError("Il progetto non ha una data di inizio nella colonna 3", pRigaInAnalisi + 1)
                End If
                If DateTime.TryParse(pFoglio.Cells(pRigaInAnalisi + 1, 3).value, pProjectStart) = False Then
                    pErrore.AddError("Il progetto sembra avere una data di inizio non valida nella colonna 3.", pRigaInAnalisi + 1)
                End If
                Do Until CStr(pFoglio.Cells(pRigaInAnalisi - 1, 3).value) = "###" And pPrimaRigaProgetto = False
                    pPrimaRigaProgetto = False
                    If CStr(pFoglio.Cells(pRigaInAnalisi, 5).value) <> "" Then
                        If DateTime.Compare(CDate(pFoglio.Cells(pRigaInAnalisi, 6).value), pProjectStart) < 0 Then
                            pErrore.AddError("La milestone contiene una data precedente l'inizio del progetto in colonna 6.", pRigaInAnalisi)
                        End If
                        If CStr(pFoglio.Cells(pRigaInAnalisi, 5).value) <> "" And CStr(pFoglio.Cells(pRigaInAnalisi, 6).value) = "" Then
                            pErrore.AddError("La milestone non ha una data nella colonna 6", pRigaInAnalisi)
                        End If
                    Else
                        If CStr(pFoglio.Cells(pRigaInAnalisi, 7).value) = "" Then
                            pErrore.AddError("La vettura non ha indicata la fase del progetto a cui appartiene nella colonna 7.", pRigaInAnalisi)
                        End If
                        If CStr(pFoglio.Cells(pRigaInAnalisi, 11).value) = "" And CStr(pFoglio.Cells(pRigaInAnalisi, 12).value) = "" And CStr(pFoglio.Cells(pRigaInAnalisi, 13).value) = "" Then
                            pErrore.AddError("La vettura non ha una percorrenza indicata nelle colonne 11, 12 e 13", pRigaInAnalisi)
                        End If
                        If System.DateTime.Compare(CDate(pFoglio.Cells(pRigaInAnalisi, 15).value), pProjectStart) < 0 Then
                            pErrore.AddError("La vettura ha una pianificazione di inizio precedente alla data di inizio del progetto nella colonna 15", pRigaInAnalisi)
                        End If
                        If CInt(pFoglio.Cells(pRigaInAnalisi, 16).value) <= 0 Then
                            pErrore.AddError("La vettura non ha una pianificazione di durata dell'accumulo nella colonna 16", pRigaInAnalisi)
                        End If
                        If DateTime.Compare(CDate(pFoglio.Cells(pRigaInAnalisi, 17).value), pProjectStart) < 0 Then
                            pErrore.AddError("La vettura ha una ripianificazione di inizio precedente alla data di inizio del progetto nella colonna 17", pRigaInAnalisi)
                        End If
                    End If
                    pRigaInAnalisi = pRigaInAnalisi + 1
                Loop
            Loop
            pExcel.Quit()
            VBACustomOperations.ExcelGenericOperations.TerminateExcelProcess(pExcel)
            pExcel = Nothing
            pFoglio = Nothing
            Return Not (pErrore.ShowErrors())

        End Function
        Public Function FileFormatVerification(ByVal DataSheetPath As String, Optional ByRef Owner As String = "", Optional ByRef Model As String = "") As Boolean
            Dim Risultato As Boolean
            Dim pExcel = New Microsoft.Office.Interop.Excel.Application
            Dim FileSystem As New System.IO.FileInfo(DataSheetPath)
            Dim pFoglio = pExcel.Workbooks.Open(DataSheetPath).Sheets(1)
            pExcel.DisplayAlerts = False
            If CStr(pFoglio.Cells(3, 1).value) = "Modello" And CStr(pFoglio.Cells(4, 1).value) = "Oggi" And CStr(pFoglio.Cells(5, 1).value) = "Owner" Then
                Model = CStr(pFoglio.Cells(3, 2).value)
                Owner = CStr(pFoglio.Cells(5, 2).value)
                Risultato = True
            Else
                MsgBox("Il file selezionato non sembra essere un file L3D valido.")
                Risultato = False
            End If
            pExcel.Workbooks.Close()
            pExcel.Quit()
            VBACustomOperations.ExcelGenericOperations.TerminateExcelProcess(pExcel)
            pExcel = Nothing
            pFoglio = Nothing
            Return Risultato
        End Function
    End Module
End Namespace
